# -*- coding: utf-8 -*-

from odoo import models, fields, api
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import psycopg2
import os
import sys


class Temporary(models.Model):
    _name = 'temporary.data'

    name = fields.Char()

    sale_order_id = fields.Integer("销售订单ID", readonly=True)
    partner = fields.Char("客户", readonly=True)
    saleman = fields.Char("销售员", readonly=True)
    crm_team = fields.Char("销售团队", readonly=True)

    # sale_order
    confirmation_date = fields.Datetime("确认日期", readonly=True)
    subscription_period = fields.Char("认购期限", readonly=True)
    amount_untaxed = fields.Float("未含税金额", readonly=True)
    amount_total = fields.Float("总计", readonly=True)
    invoice_status = fields.Char("发票状态", readonly=True)

    # product_template
    product_name = fields.Char("产品名", readonly=True)
    product_type = fields.Char("产品类型", readonly=True)
    list_price = fields.Float("标价", readonly=True)
    is_financial = fields.Boolean("金融产品", readonly=True)
    annual_interest = fields.Float("年利率", readonly=True)
    sale_bonus = fields.Float("产品提成", readonly=True)
    type_financial = fields.Char("金融产品类型", readonly=True)

    # sale_order_line
    product_uom_qty = fields.Float("订购数量", readonly=True)
    qty_invoiced = fields.Float("已开发票数量", readonly=True)
    price_unit = fields.Float("单价", readonly=True)
    product_description = fields.Char("产品说明", readonly=True)

    # account_invoice
    invoice_number = fields.Char("发票号码", readonly=True)
    date_invoice = fields.Date("开票日期", readonly=True)
    origin = fields.Char("源单据", readonly=True)
    date_due = fields.Date("截止日期", readonly=True)
    residual = fields.Float("截止金额", readonly=True)

    # 新增还款五个字段
    repayment_times_total = fields.Integer("还款总期数", readonly=True)
    repayment_time = fields.Integer("第几期还款", readonly=True)
    repayment_datetime = fields.Datetime("本期还款日期", readonly=True)
    repayment_amount = fields.Float("本期还款数额", readonly=True)
    repayment_state = fields.Boolean("还款状态", default=False, readonly=True)

    # 读取并插入
    def readInsert(self):
        conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
        cursor = conn.cursor()
        cursor.execute(
            "insert into public.temporary_data("
            "sale_order_id, partner, crm_team, "
            "confirmation_date, subscription_period, amount_untaxed, amount_total, invoice_status, "
            "product_name, product_type, list_price, is_financial, annual_interest, sale_bonus, type_financial, "
            "product_uom_qty, qty_invoiced, price_unit, product_description, "
            "invoice_number, date_invoice, origin, date_due, residual) "
            "SELECT public.sale_order.id AS 销售订单, " \
            "public.res_partner.name AS 客户, " \
            "public.crm_team.name AS 销售团队," \
            "public.sale_order.confirmation_date AS 确认日期, " \
            "public.sale_order.subscription_period AS 认购期限, " \
            "public.sale_order.amount_untaxed AS 未含税金额, " \
            "public.sale_order.amount_total AS 总计, " \
            "public.sale_order.invoice_status AS 发票状态, " \
            "public.product_template.name AS 产品名,	" \
            "public.product_template.type AS 产品类型, " \
            "public.product_template.list_price AS 标价,	" \
            "public.product_template.is_financial AS 金融产品, " \
            "public.product_template.annual_interest AS 年利率, " \
            "public.product_template.sale_bonus AS 产品提成, " \
            "public.product_template.type_financial AS 金融产品类型, " \
            "public.sale_order_line.product_uom_qty AS 订购数量, " \
            "public.sale_order_line.qty_invoiced AS 已开发票数量, " \
            "public.sale_order_line.price_unit AS 单价, " \
            "public.sale_order_line.name AS 产品说明, " \
            "public.account_invoice.number AS 发票号码, " \
            "public.account_invoice.date_invoice AS 开票日期, " \
            "public.account_invoice.origin AS 源单据, " \
            "public.account_invoice.date_due AS 截止日期, " \
            "public.account_invoice.residual AS 截止金额 " \
            "FROM public.sale_order " \
            "INNER JOIN public.sale_order_line ON public.sale_order_line.order_id = public.sale_order.id " \
            "INNER JOIN public.res_partner ON public.sale_order.partner_id = public.res_partner.id " \
            "INNER JOIN public.product_product ON public.sale_order_line.product_id = public.product_product.id " \
            "INNER JOIN public.product_template ON public.product_product.product_tmpl_id = public.product_template.id " \
            "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
            "LEFT JOIN public.crm_team ON public.crm_team.id = public.res_users.sale_team_id " \
            "INNER JOIN public.account_invoice ON public.account_invoice.partner_id = public.res_partner.id " \
            "WHERE public.sale_order.invoice_status = 'invoiced'")
        conn.commit()
        conn.close()

    # 借款
    def calculateLoan(self):
        conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
        cursor = conn.cursor()
        cursor.execute("select * from public.temporary_data where sale_order_id is not null")
        rows = cursor.fetchall()
        for i in range(len(rows)):
            confirm_date = datetime.strptime(rows[i][18], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
            periods = int(rows[i][29]) / 1  # 还款次数
            interest = rows[i][28] * rows[i][15] / 12 * 3  # 利息
            principal_interest = rows[i][28] + interest  # 本息
            if confirm_date.day > 10 & confirm_date.day <= 25:
                first_repay_date = confirm_date.replace(day=25)  # 第一次还款日期
            else:
                first_repay_date = confirm_date.replace(day=10)  # 第一次还款日期
            if int(rows[i][27]) == 1:  # 借款
                for period in range(periods):
                    repay_date = first_repay_date + relativedelta(months=+1) * period
                    if period == (periods - 1):
                        cursor.execute("insert into public.temporary_data("
                                       "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                       "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                       "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                       "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                       "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                       "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                       (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                        rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                        rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                        rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                        periods, periods, repay_date, principal_interest))
                        conn.commit()
                    else:
                        cursor.execute("insert into public.temporary_data("
                                       "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                       "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                       "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                       "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                       "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                       "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                       (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                        rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                        rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                        rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                        periods, (period + 1), repay_date, interest))
                        conn.commit()
        cursor.close()
        conn.close()

    # 基金
    def calculateFund(self):
        conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
        cursor = conn.cursor()
        cursor.execute("select * from public.temporary_data where sale_order_id is not null")
        rows = cursor.fetchall()
        for i in range(len(rows)):
            confirm_date = datetime.strptime(rows[i][18], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
            periods = int(rows[i][29]) / 3  # 还款次数
            interest = rows[i][28] * rows[i][15] / 12 * 3  # 利息
            principal_interest = rows[i][28] + interest  # 本息
            first_repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 第一次还款日期
            if int(rows[i][27]) == 2:  # 基金
                for period in range(periods):
                    repay_date = first_repay_date + relativedelta(months=+3) * period
                    if period == (periods - 1):
                        cursor.execute("insert into public.temporary_data("
                                       "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                       "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                       "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                       "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                       "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                       "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                       (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                        rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                        rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                        rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                        periods, periods, repay_date, principal_interest))
                        conn.commit()
                    else:
                        cursor.execute("insert into public.temporary_data("
                                       "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                       "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                       "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                       "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                       "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                       "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                       (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                        rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                        rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                        rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                        periods, (period + 1), repay_date, interest))
                        conn.commit()
        cursor.close()
        conn.close()

# 读取并插入
def readInsertOperate():
    conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
    cursor = conn.cursor()
    cursor.execute(
        "insert into public.temporary_data("
        "sale_order_id, partner, crm_team, "
        "confirmation_date, subscription_period, amount_untaxed, amount_total, invoice_status, "
        "product_name, product_type, list_price, is_financial, annual_interest, sale_bonus, type_financial, "
        "product_uom_qty, qty_invoiced, price_unit, product_description, "
        "invoice_number, date_invoice, origin, date_due, residual) "
        "SELECT public.sale_order.id AS 销售订单, " \
        "public.res_partner.name AS 客户, " \
        "public.crm_team.name AS 销售团队," \
        "public.sale_order.confirmation_date AS 确认日期, " \
        "public.sale_order.subscription_period AS 认购期限, " \
        "public.sale_order.amount_untaxed AS 未含税金额, " \
        "public.sale_order.amount_total AS 总计, " \
        "public.sale_order.invoice_status AS 发票状态, " \
        "public.product_template.name AS 产品名,	" \
        "public.product_template.type AS 产品类型, " \
        "public.product_template.list_price AS 标价,	" \
        "public.product_template.is_financial AS 金融产品, " \
        "public.product_template.annual_interest AS 年利率, " \
        "public.product_template.sale_bonus AS 产品提成, " \
        "public.product_template.type_financial AS 金融产品类型, " \
        "public.sale_order_line.product_uom_qty AS 订购数量, " \
        "public.sale_order_line.qty_invoiced AS 已开发票数量, " \
        "public.sale_order_line.price_unit AS 单价, " \
        "public.sale_order_line.name AS 产品说明, " \
        "public.account_invoice.number AS 发票号码, " \
        "public.account_invoice.date_invoice AS 开票日期, " \
        "public.account_invoice.origin AS 源单据, " \
        "public.account_invoice.date_due AS 截止日期, " \
        "public.account_invoice.residual AS 截止金额 " \
        "FROM public.sale_order " \
        "INNER JOIN public.sale_order_line ON public.sale_order_line.order_id = public.sale_order.id " \
        "INNER JOIN public.res_partner ON public.sale_order.partner_id = public.res_partner.id " \
        "INNER JOIN public.product_product ON public.sale_order_line.product_id = public.product_product.id " \
        "INNER JOIN public.product_template ON public.product_product.product_tmpl_id = public.product_template.id " \
        "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
        "LEFT JOIN public.crm_team ON public.crm_team.id = public.res_users.sale_team_id " \
        "INNER JOIN public.account_invoice ON public.account_invoice.partner_id = public.res_partner.id " \
        "WHERE public.sale_order.invoice_status = 'invoiced'")
    conn.commit()
    conn.close()


# 借款
def calculateLoan():
    conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
    cursor = conn.cursor()
    cursor.execute("select * from public.temporary_data where sale_order_id is not null")
    rows = cursor.fetchall()
    for i in range(len(rows)):
        confirm_date = datetime.strptime(rows[i][18], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
        periods = int(rows[i][29]) / 1  # 还款次数
        interest = rows[i][28] * rows[i][15] / 12 * 3  # 利息
        principal_interest = rows[i][28] + interest  # 本息
        if confirm_date.day > 10 & confirm_date.day <= 25:
            first_repay_date = confirm_date.replace(day=25)  # 第一次还款日期
        else:
            first_repay_date = confirm_date.replace(day=10)  # 第一次还款日期
        if int(rows[i][27]) == 1:  # 借款
            for period in range(periods):
                repay_date = first_repay_date + relativedelta(months=+1) * period
                if period == (periods - 1):
                    cursor.execute("insert into public.temporary_data("
                                   "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                   "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                   "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                   "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                   "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                   "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                   (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                    rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                    rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                    rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                    periods, periods, repay_date, principal_interest))
                    conn.commit()
                else:
                    cursor.execute("insert into public.temporary_data("
                                   "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                   "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                   "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                   "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                   "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                   "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                   (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                    rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                    rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                    rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                    periods, (period + 1), repay_date, interest))
                    conn.commit()
    cursor.close()
    conn.close()

# 基金
def calculateFund():
    conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
    cursor = conn.cursor()
    cursor.execute("select * from public.temporary_data where sale_order_id is not null")
    rows = cursor.fetchall()
    for i in range(len(rows)):
        confirm_date = datetime.strptime(rows[i][18], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
        periods = int(rows[i][29]) / 3  # 还款次数
        interest = rows[i][28] * rows[i][15] / 12 * 3  # 利息
        principal_interest = rows[i][28] + interest  # 本息
        first_repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 第一次还款日期
        if int(rows[i][27]) == 2:  # 基金
            for period in range(periods):
                repay_date = first_repay_date + relativedelta(months=+3) * period
                if period == (periods - 1):
                    cursor.execute("insert into public.temporary_data("
                                   "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                   "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                   "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                   "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                   "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                   "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                   (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                    rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                    rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                    rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                    periods, periods, repay_date, principal_interest))
                    conn.commit()
                else:
                    cursor.execute("insert into public.temporary_data("
                                   "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, "
                                   "amount_total, invoice_status, product_name, product_type, list_price, is_financial, "
                                   "annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, "
                                   "product_description, invoice_number, date_invoice, origin, date_due, residual, "
                                   "repayment_times_total, repayment_time, repayment_datetime, repayment_amount) "
                                   "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                   (rows[i][23], rows[i][9], rows[i][19], rows[i][18], rows[i][29], rows[i][12],
                                    rows[i][28], rows[i][31], rows[i][16], rows[i][26], rows[i][3], rows[i][13],
                                    rows[i][15], rows[i][14], rows[i][27], rows[i][5], rows[i][6], rows[i][4],
                                    rows[i][33], rows[i][8], rows[i][22], rows[i][1], rows[i][2], rows[i][20],
                                    periods, (period + 1), repay_date, interest))
                    conn.commit()
    cursor.close()
    conn.close()


def SaleManOperate():
    conn = psycopg2.connect(database="odoo", user="gc740", password="123456", host="127.0.0.1", port="5432")
    cursor = conn.cursor()
    cursor.execute(
        "insert into public.temporary_data(saleman) "
        "SELECT public.res_partner.display_name AS 销售员 " \
        "FROM public.sale_order " \
        "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
        "INNER JOIN public.res_partner ON public.res_users.partner_id = public.res_partner.id " \
        "WHERE public.sale_order.invoice_status = 'invoiced'")
    conn.commit()
    conn.close()
    print 'read and insert records successfully'

if __name__ == '__main__':
    # readInsertOperate()
    calculateLoan()
    calculateFund()
    # SaleManOperate()
